DECLARE @ModelPublicationId UNIQUEIdENTIFIER
DECLARE @ServicePartsPageId UNIQUEIdENTIFIER
DECLARE @LanguageCode NVARCHAR(MAX)
DECLARE @ModelId UNIQUEIdENTIFIER
DECLARE @BillOfMaterialsHeaderId UNIQUEIDENTIFIER

SELECT
    BillOfMaterialsLine.PartNumber
FROM
    BillOfMaterialsLine
WHERE
    BillOfMaterialsHeaderId = @BillOfMaterialsHeaderId
    AND (
         (
          BillOfMaterialsLine.MainLinkExcludedFrom = 0
          AND EXISTS ( SELECT
                        1
                       FROM
                        MainPartLink
                       WHERE
                        MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                        AND MainPartLink.ModelPublicationId = @ModelPublicationId
                        AND MainPartLink.ServicePartsPageId <> @ServicePartsPageId )
         )
         OR (
             BillOfMaterialsLine.InnerLinkExcludedFrom = 0
             AND EXISTS ( SELECT
                            1
                          FROM
                            InnerPartLink
                            INNER JOIN Model
                                ON InnerPartLink.ModelCode = Model.ModelCode
                                   AND InnerPartLink.ModelSequence = Model.ModelSequence
                            INNER JOIN Publication
                                ON InnerPartLink.PublicationId = Publication.PublicationId
                            INNER JOIN LocalizedPublication
                                ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId
                                   AND LocalizedPublication.LanguageCode = @LanguageCode
                          WHERE
                            InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                            AND Model.ModelId = @ModelId
                            AND Publication.FollowingPublicationId IS NULL )
            )
        )


